package com.qxf.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.qxf.exception.UserConfigurationException;
import com.qxf.model.beans.AdpBean;
import com.qxf.model.beans.MediaBean;
import com.qxf.model.beans.UserSession;
import com.qxf.util.Constants;
import com.qxf.util.db.PageDivision;
import com.qxf.util.db.SQLProcessor;
import com.qxf.util.db.SQLUtil;

public class AdpositionMgtDao {

	static Logger log = Logger.getLogger(AdpositionMgtDao.class);
	
	/**
	 * 
	 * @param userSession
	 * @return
	 * @throws SQLException
	 * @deprecated
	 */
	@SuppressWarnings("rawtypes")
	public String getAdList(UserSession userSession) throws SQLException {
		SQLProcessor sp = null;
		String sql = null;
		List list = null;
		try{
			sp = new SQLProcessor();
			sql = "select count(*) as c from tb_user where user_name = " + SQLUtil.toSqlString("") + 
				" and user_password = " + SQLUtil.toSqlString("");
			log.info(sql);
			list = sp.executeQuery(sql);
			Map map = (HashMap)(list.listIterator().next());
			int count = (Integer)map.get("c");
			if(count == 1) {
			}
		}catch(SQLException se){
			sp.rollback();
			se.printStackTrace();
			throw se;
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static List<AdpBean> getAdpList(String searchCondition, PageDivision pd) {
		String sql = null;
		SQLProcessor sp = null;
		List<AdpBean> adpList = new ArrayList<AdpBean>();
		try{
			sp = new SQLProcessor();
			sql = "select a.ad_id as ad_id, a.ad_name as ad_name, a.ad_url as ad_url, a.ad_clickcount as ad_clickcount," +
					" m.media_id as media_id, m.media_name as media_name, t.activity_id as activity_id" +
					" from tb_ad a" +
					" left join tb_media m on a.media_id=m.media_id" +
					" left join tb_activity t on a.activity_id=t.activity_id"
				 + " where 1=1 "
				 + (searchCondition!=null?(" " + searchCondition) : "");
			List<Map<String, Object>> list = pd!=null?sp.executeQuery(sql, pd.getBegin(), pd.getPageSize()):sp.executeQuery(sql);
			for(int i=0; i<list.size(); i++){
				AdpBean adp = new AdpBean();
				Map<String, Object> adpMap = list.get(i);
				adp.setAdId(String.valueOf((Integer)adpMap.get("ad_id")));
				adp.setAdName((String)adpMap.get("ad_name"));
				adp.setMediaId(String.valueOf((Integer)adpMap.get("media_id")));
				adp.setMediaName((String)adpMap.get("media_name"));
				adp.setAdUrl((String)adpMap.get("ad_url"));
				adp.setCount(String.valueOf((Integer)adpMap.get("ad_clickcount")));
				adpList.add(adp);
			}
			log.info("SQL: " + sql);
		}catch(SQLException se){
			log.info("Error SQL: " + sql);
			se.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return adpList;
	}
	
	@SuppressWarnings("unchecked")
	public String getAdUrl(String adId) throws Exception {
		String sql = null;
		SQLProcessor sp = null;
		String adUrl = null;
		List<Map<String, Object>> list = null;
		try {
			sp = new SQLProcessor();
			sql = "select ad_url from tb_ad where ad_id=" + SQLUtil.toSqlInt(adId);
			list = sp.executeQuery(sql);
			if(list.size() == 1) {
				Map<String, Object> map = list.get(0);
				adUrl = (String)map.get("ad_url");
			}
			if(adUrl == null || adUrl.trim().length() == 0) {
				throw new UserConfigurationException("该链接链接异常");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SQLProcessor.staticClose(sp);
		}
		return adUrl;
	}
	
	public String generateAdUrl(String servletUrl, String adId, String activityUrl) throws SQLException {
		String sql = null;
		SQLProcessor sp = null;
		String adUrl = null;
		int flag = -1;
		try{
			adUrl = servletUrl + "/t.do?f=" + adId + "&a=" + activityUrl;
			log.info("generateAdUrl(): URL=" + adUrl);
			sp = new SQLProcessor();
			sql = "update tb_ad set ad_url=" + SQLUtil.toSqlString(adUrl) + " where ad_id=" + SQLUtil.toSqlInt(adId);
			log.info("generateAdUrl(): SQL=" + sql);
			flag = sp.executeUpdate(sql);
			if(flag == -1) {
				return Constants.RETURN_FAILURE;
			}
		}catch(SQLException se){
			log.info("Error SQL: " + sql);
			sp.rollback();
			se.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return adUrl;
	}
	
	public boolean addAd(String mediaId, String adName, String activityId) throws Exception {
		String sql = null;
		SQLProcessor sp = null;
		int flag = -1;
		try{
			sp = new SQLProcessor();
			sql = "insert into tb_ad(ad_name,media_id,activity_id) values(" +
					SQLUtil.toSqlString(adName) + "," +
					SQLUtil.toSqlInt(mediaId) + "," +
					SQLUtil.toSqlInt(activityId) +
					")";
			log.info("addAd(): SQL=" + sql);
			flag = sp.executeUpdate(sql);
			if(flag == -1) {
				return false;
			}
		}catch(SQLException se){
			log.info("addAd(): Error SQL=" + sql);
			sp.rollback();
			se.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return true;
	}
	
	public boolean editAd(String mediaId, String adName) throws Exception {
		String sql = null;
		SQLProcessor sp = null;
		int flag = -1;
		try{
			sp = new SQLProcessor();
			sql = "update tb_ad set ad_name=" + SQLUtil.toSqlString(adName) + 
					" where ad_id=" + SQLUtil.toSqlInt(mediaId);
			log.info("editAd(): SQL=" + sql);
			flag = sp.executeUpdate(sql);
			if(flag == -1) {
				return false;
			}
		}catch(SQLException se){
			log.info("editAd(): Error SQL=" + sql);
			sp.rollback();
			se.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return true;
	}
	
	@SuppressWarnings("unchecked")
	public List<MediaBean> getMediaList(String userId) {
		String sql = null;
		SQLProcessor sp = null;
		List<Map<String, Object>> list = null;
		List<MediaBean> mediaList = new ArrayList<MediaBean>();
		try {
			sp = new SQLProcessor();
			sql = "select m.media_id, m.media_name" +
					" from tb_media m" +
					" left join tb_user u on m.user_id=u.user_id" +
					" where u.user_id=" + SQLUtil.toSqlInt(userId);
			list = sp.executeQuery(sql);
			log.info("getMediaList(): SQL=" + sql);
			MediaBean media = null;
			for(int i=0; i<list.size(); i++){
				media = new MediaBean();
				Map<String, Object> map = list.get(i);
				media.setMediaId(String.valueOf((Integer)map.get("media_id")));
				media.setMediaName((String)map.get("media_name"));
				mediaList.add(media);
			}
		} catch (SQLException e) {
			log.info("getMediaList(): Error SQL=" + sql);
			e.printStackTrace();
		} finally {
			SQLProcessor.staticClose(sp);
		}
		return mediaList;
	}
	
	@SuppressWarnings("unchecked")
	public AdpBean getAdConfiguration(String userId, String adId) throws Exception {
		String sql = null;
		SQLProcessor sp = null;
		List<Map<String, Object>> list = null;
		AdpBean adp = null;
		List<MediaBean> mediaList = getMediaList(userId);
		
		try {
			sp = new SQLProcessor();
			sql = "select a.ad_id,a.ad_name,a.ad_url,m.media_id,m.media_name" +
					" from tb_ad a" +
					" left join tb_media m on a.media_id=m.media_id" +
					" where a.ad_id=" + SQLUtil.toSqlInt(adId);
			list = sp.executeQuery(sql);
			log.info("getAdConfiguration(): SQL=" + sql);
			if(list.size() == 1) {
				adp = new AdpBean();
				Map<String, Object> map = list.get(0);
				adp.setMediaId(String.valueOf((Integer)map.get("media_id")));
				adp.setMediaName((String)map.get("media_name"));
				adp.setAdId(String.valueOf((Integer)map.get("ad_id")));
				adp.setAdName((String)map.get("ad_name"));
				adp.setAdUrl((String)map.get("ad_url"));
				adp.setMediaInfoList(mediaList);
			} else if(list.size() == 0) {
				throw new UserConfigurationException("该广告位信息异常");
			}
		} catch (SQLException e) {
			log.info("getAdConfiguration(): Error SQL=" + sql);
			e.printStackTrace();
		} finally {
			SQLProcessor.staticClose(sp);
		}
		return adp;
	}
	
	public boolean addMedia(String mediaName, String userId) throws Exception {
		String sql = null;
		SQLProcessor sp = null;
		int flag = -1;
		try{
			sp = new SQLProcessor();
			sql = "insert into tb_media(media_name,user_id) values(" +
					SQLUtil.toSqlString(mediaName) + ", " +
					SQLUtil.toSqlInt(userId) +
					")";
			log.info("addMedia(): SQL=" + sql);
			flag = sp.executeUpdate(sql);
			if(flag == -1) {
				return false;
			}
		}catch(SQLException se){
			log.info("addMedia(): Error SQL=" + sql);
			sp.rollback();
			se.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return true;
	}
	
	public boolean deleteAd(String adId) throws Exception {
		String sql = null;
		SQLProcessor sp = null;
		int flag = -1;
		try{
			sp = new SQLProcessor();
			sql = "delete from tb_ad where ad_id=" + SQLUtil.toSqlInt(adId);
			log.info("deleteAd(): SQL=" + sql);
			flag = sp.executeUpdate(sql);
			if(flag == -1) {
				return false;
			}
		}catch(SQLException se){
			log.info("deleteAd(): Error SQL=" + sql);
			sp.rollback();
			se.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return true;
	}
}
